﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Exchange.WebServices.Data;
using Microsoft.Exchange.WebServices.Authentication;
using System.Text.RegularExpressions;

namespace rfiInbox
{
    public class rfiregex
    {
        ExchangeService service = Service.ConnectToService(UserData.CreateUserData("rfi@rhaaia.com"), new TraceListener());
        string rfi_vendor_number;
        string bid_question;
        string reverse;
        string str;
        string seq;
        DataRow row;
        int count;
        int i;
        string subject;
        string subject_and_body_and_attachment;
        mysqlconn con;
        string category;
        DateTime current;

        public void rficoding()
        {
            try
            {
                Mailbox rfi_in = new Mailbox("rfi@rhaaia.com");
                FolderId rfi_inbox = new FolderId(WellKnownFolderName.Inbox, rfi_in);
                ItemView iv = new ItemView(int.MaxValue);
                FindItemsResults<Item> rfis = service.FindItems(rfi_inbox, iv);
                DataTable report = new DataTable();
                current = DateTime.Now;
                report.Columns.Add("cat", typeof(string));
                report.Columns.Add("typ", typeof(string));
                report.Columns.Add("to", typeof(string));
                report.Columns.Add("recd", typeof(DateTime));
                report.Columns.Add("from", typeof(string)); 
                report.Columns.Add("sent", typeof(DateTime));
                HashSet<DateTime> holidays = holiday_calendar(current.Year);
                categorize(rfis, report);                
                overdue(report, holidays);
            }

            catch (Exception ex)
            {
                string subject = "Error in iteration";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        HashSet<DateTime> holiday_calendar(int year)
        {
            HashSet<DateTime> hc = new HashSet<DateTime>();
            for (int y = year - 1; y < year + 2; y++)
            { hc.UnionWith(GetHolidays(y)); }
            return hc;
        }


        public void categorize(FindItemsResults<Item> rfis, DataTable reprt)
        {
            try
            {
                DataTable report = reprt;
                count = rfis.Items.Count;

                for (i = 0; i < count; i++)
                {
                    Item item = rfis.Items[i];
                    ItemId id = item.Id;
                    EmailMessage rfiem = EmailMessage.Bind(service, id, new PropertySet(BasePropertySet.FirstClassProperties, ItemSchema.Attachments));
                    StringList cat = item.Categories;                   
                    
                    if (rfiem.HasAttachments == true)
                    {subject_and_body_and_attachment = rfiem.Subject.ToString() + rfiem.Body.Text.ToString() + rfiem.Attachments[0].Name;}
                    else
                    {subject_and_body_and_attachment = rfiem.Subject.ToString() + rfiem.Body.Text.ToString();}
                    subject = rfiem.Subject.ToString().Trim().Replace(",", " ");                    
                    string str_and_seq = Regex.Match(subject_and_body_and_attachment, @"(\d{3,4}-\d{1,3}\s*)").ToString();
                    if (!String.IsNullOrEmpty(str_and_seq))
                    {
                        con = new mysqlconn();
                        string[] strseq = str_and_seq.Split('-');
                        str = strseq[0];
                        seq = int.Parse(strseq[1]).ToString();
                        row = con.loadstores(str, seq);
                    }
                    if (String.IsNullOrEmpty(str_and_seq) || row == null)
                    {
                        con = new mysqlconn();
                        subject_and_body_and_attachment = Regex.Replace(subject_and_body_and_attachment, @"<[^>]*>", String.Empty);
                        str = Regex.Match(subject_and_body_and_attachment, @"\d{3,4}").ToString().Trim();
                        seq = "NS";
                        row = con.loadstores(str);
                    }

                    string rfi_number = Regex.Match(Regex.Match(subject_and_body_and_attachment, @"([Rr]{1}[Ff]{1}[Ii]{1})(\W*)(\s*)(\d+)").ToString().Trim(), @"\d+").ToString().Trim();
                    if (!String.IsNullOrEmpty(rfi_number))
                    {
                        int len = rfi_number.Length;
                        if (len == 1) rfi_number = "00" + rfi_number;
                        else if (len == 2) rfi_number = "0" + rfi_number;
                    }

                    rfi_vendor_number = Regex.Match(Regex.Match(subject_and_body_and_attachment, @"([Rr]{1}[Ff]{1}[Ii]{1})(\s*)(\W*)(\s*)[NnUuMmBbEeRr]{6}(\W*)(\s*)([Vv]{1}\d+)-(\d+)-(\d+)").ToString().Trim(),
                                        @"([Vv]{1}\d+)-(\d+)-(\d+)").ToString().Trim();

                    if (!String.IsNullOrEmpty(rfi_number) && row != null)
                    {
                        item.Categories.Clear();
                        category = row["city"] + " " + row["st"] + " " + str + "-" + seq + " - RFI: " + rfi_number;
                        item.Categories.Add(category);
                        item.Update(ConflictResolutionMode.AlwaysOverwrite);
                    }

                    else if (!String.IsNullOrEmpty(rfi_vendor_number))
                    {
                        item.Categories.Clear();
                        category = row["city"] + " " + row["st"] + " " + str + "-" + seq + " - RFI: " + rfi_vendor_number;
                        item.Categories.Add(category);
                        item.Update(ConflictResolutionMode.AlwaysOverwrite);
                    }
                    else if (String.IsNullOrEmpty(rfi_number) && String.IsNullOrEmpty(rfi_vendor_number) && row != null)
                    {
                        bid_question = Regex.Match(subject_and_body_and_attachment, @"([Bb]{1}[Ii]{1}[Dd]{1})").ToString().Trim();
                        reverse = Regex.Match(subject_and_body_and_attachment, @"[RrEeVvEeRrSsEs]{7}").ToString().Trim();
                        if (String.IsNullOrEmpty(reverse))
                        {
                            subject_and_body_and_attachment = subject_and_body_and_attachment.Replace(",", String.Empty);
                            int question_index = subject_and_body_and_attachment.IndexOf("Question:");
                            int reference_index = subject_and_body_and_attachment.IndexOf("Reference");
                            if (question_index > 0 && reference_index < 1)
                            {
                                string question = Regex.Replace(subject_and_body_and_attachment.Substring(question_index + 10, 75), @"\s+[\r\n\?#!$%&,<>()]+", String.Empty);
                                item.Categories.Clear();
                                category = row["city"] + " " + row["st"] + " " + str + "-" + seq + " - Bid Question: " + question;
                                item.Categories.Add(category);
                                item.Update(ConflictResolutionMode.AlwaysOverwrite);
                            }
                            else if (reference_index > 0 && question_index < 1)
                            {
                                string reference = Regex.Match(subject_and_body_and_attachment.Substring(reference_index + 10, 75), @"[A-Z\s+]+").ToString().Trim();
                                item.Categories.Clear();
                                category = row["city"] + " " + row["st"] + " " + str + "-" + seq + " - Bid Question: " + reference;
                                item.Categories.Add(category);
                                item.Update(ConflictResolutionMode.AlwaysOverwrite);
                            }

                            else
                            {
                                item.Categories.Clear();
                                category = row["city"] + " " + row["st"] + " " + str + "-" + seq + " - " + subject + "'";
                                item.Categories.Add(category);
                                item.Update(ConflictResolutionMode.AlwaysOverwrite);
                            }
                        }
                        else if (!String.IsNullOrEmpty(reverse))
                        {
                            item.Categories.Clear();
                            category = row["city"] + " " + row["st"] + " " + str + "-" + seq + " - REVERSE RFI";
                            item.Categories.Add(category);
                            item.Update(ConflictResolutionMode.AlwaysOverwrite);
                        }
                    }

                    else if (row != null)
                    {
                        item.Categories.Clear();
                        category = row["city"] + " " + row["st"] + " " + subject + "'";
                        item.Categories.Add(category);
                        item.Update(ConflictResolutionMode.AlwaysOverwrite);
                    }

                    else
                    {
                        item.Categories.Clear();
                        category = " Insufficient Information to Categorize";
                        item.Categories.Add(category);
                        item.Update(ConflictResolutionMode.AlwaysOverwrite);
                    }
                    Console.WriteLine("'" + i + "' of '" + count + "'");
                    table(category, row, item, report, rfiem);
                }
            }
            catch (Exception ex)
            {
                string subject = "Error in categorizing";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        public void table(string cat, DataRow rw, Item itm, DataTable report, EmailMessage rfie)
        {
            try
            {
                DataRow report_row = report.NewRow();
                report_row["cat"] = cat;
                if (rw != null) report_row["typ"] = rw["prj_typ"];
                report_row["to"] = rfie.ReceivedBy.Name;
                report_row["recd"] = itm.DateTimeReceived;
                report_row["from"] = rfie.Sender.Name;
                report_row["sent"] = itm.DateTimeSent;
                report.Rows.Add(report_row);
            }
            catch (Exception ex)
            {
                string subject = "Error in creating report table";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        public void table_3(string rfi, DataTable three_day, string to2, TimeSpan time_remain)
        {
            try
            {
                DataRow overdue_row = three_day.NewRow();
                overdue_row["rfi"] = rfi;
                overdue_row["who"] = to2;
                overdue_row["time_remain"] = time_remain;
                three_day.Rows.Add(overdue_row);
            }
            catch (Exception ex)
            {
                string subject = "Error in creating report table";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        public void table_5(string rfi, DataTable five_day, string to2, TimeSpan time_remain)
        {
            try
            {
                DataRow overdue_row = five_day.NewRow();
                overdue_row["rfi"] = rfi;
                overdue_row["who"] = to2;
                overdue_row["time_remain"] = time_remain;
                five_day.Rows.Add(overdue_row);
            }
            catch (Exception ex)
            {
                string subject = "Error in creating report table";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        public void table_rha(string rfi, DataTable rha, TimeSpan time_remain)
        {
            try
            {
                DataRow inform = rha.NewRow();
                inform["rfi"] = rfi;
                inform["time"] = time_remain;
                rha.Rows.Add(inform);
            }
            catch (Exception ex)
            {
                string subject = "Error in creating report table";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        public void table_consultant(string rfi, DataTable consultant, TimeSpan time_remain)
        {
            try
            {
                DataRow inform = consultant.NewRow();
                inform["rfi"] = rfi;
                inform["time"] = time_remain;
                consultant.Rows.Add(inform);
            }
            catch (Exception ex)
            {
                string subject = "Error in creating report table";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        public void table_old_rfi(string rfi, DataTable old_rfi, TimeSpan time_remain)
        {
            try
            {
                DataRow inform = old_rfi.NewRow();
                inform["rfi"] = rfi;
                inform["time"] = time_remain;
                old_rfi.Rows.Add(inform);
            }
            catch (Exception ex)
            {
                string subject = "Error in creating report table";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }        

        public void overdue(DataTable report, HashSet<DateTime> holidays)
        {
            try
            {
                DataRow emailrow;
                emailrow = con.loademail();
                DateTime last_update = Convert.ToDateTime(emailrow["date_time"]);
                TimeSpan ts_seven_thirty = new TimeSpan(7, 30, 0);
                TimeSpan ts_nine = new TimeSpan(9, 0, 0);
                TimeSpan ts_six = new TimeSpan(6, 0, 0);
                TimeSpan ts_two = new TimeSpan(14, 0, 0);
                TimeSpan ts_five_thirty = new TimeSpan(17, 30, 0);
                DataTable distinct = report.DefaultView.ToTable(true, "cat");
                DataTable three_day = new DataTable();
                three_day.Columns.Add("rfi", typeof(string));
                three_day.Columns.Add("who", typeof(string));
                three_day.Columns.Add("time_remain", typeof(DateTime));
                DataTable five_day = new DataTable();
                five_day.Columns.Add("rfi", typeof(string));
                five_day.Columns.Add("who", typeof(string));
                five_day.Columns.Add("time_remain", typeof(DateTime));
                DataTable rha = new DataTable();
                rha.Columns.Add("rfi", typeof(string));
                rha.Columns.Add("time", typeof(string));
                DataTable consultant = new DataTable();
                consultant.Columns.Add("rfi", typeof(string));
                consultant.Columns.Add("time", typeof(string));
                DataTable old_rfi = new DataTable();
                old_rfi.Columns.Add("rfi", typeof(string));
                old_rfi.Columns.Add("time", typeof(string));                
                TimeSpan seventy_two = new TimeSpan(72, 0, 0);               
                TimeSpan one_twenty = new TimeSpan(120, 0, 0);

                int distinct_rows = distinct.Rows.Count;

                for (int i = 0; i < distinct_rows; i++)
                {
                    string category = distinct.Rows[i]["cat"].ToString();
                    DataRow[] cat_collection;
                    cat_collection = report.Select("cat = '" + category + "'", "recd asc");
                    int cat_length = cat_collection.Length;
                    string from1 = cat_collection[0]["from"].ToString();
                    string to1 = cat_collection[0]["to"].ToString();
                    DateTime time1 = DateTime.Parse(cat_collection[0]["recd"].ToString());                    
                    string rfi = cat_collection[0]["cat"].ToString();
                    string from2 = null;
                    DateTime time2 = new DateTime();
                    string to2 = null;
                    TimeSpan elapsed_time = current - time1;
                    TimeSpan rhw = remove_holidays_weekends(cat_collection, holidays);
                    TimeSpan rnb = remove_non_business_hours(current, time1);
                    TimeSpan time_remain1 = seventy_two - rhw - elapsed_time - rnb;
                    TimeSpan time_remain2 = one_twenty - remove_holidays_weekends(cat_collection, holidays) - elapsed_time - remove_non_business_hours(current, time1);

                    if (time_remain1 < ts_six) table_3(rfi, three_day, to2, time_remain1);
                    else if (time_remain2 < ts_six) table_5(rfi, five_day, to2, time_remain2);
                    
                    if (cat_length > 1)
                    {
                        from2 = cat_collection[cat_collection.Length - 1]["from"].ToString();
                        time2 = DateTime.Parse(cat_collection[cat_collection.Length - 1]["recd"].ToString());
                        to2 = cat_collection[cat_collection.Length - 1]["to"].ToString();
                    }
                    
                    if (from1 == "Amy Mills" && to1 == "RFI")
                    {                        
                        if (from1 == "Amy Mills" && to1 == "RFI" && cat_length == 1)
                        {
                            TimeSpan ts = remove_non_business_hours(current, time1);
                            TimeSpan one_hour = new TimeSpan(1, 0, 0);
                            if (ts > one_hour) table_rha(rfi, rha, ts);                            
                        }

                        else if (to2 == "RFI" && cat_length > 1)
                        {
                            TimeSpan ts = remove_non_business_hours(current, time2);
                            TimeSpan two_hours = new TimeSpan(2, 0, 0);
                            if (ts > two_hours) table_rha(rfi, rha, ts);
                        }
                        
                        else if (from1 == "Amy Mills" && from2 == "RFI" && cat_length > 1)
                        {
                            TimeSpan ts_vendor = remove_non_business_hours(current, time2);
                            TimeSpan eight_hours = new TimeSpan(8, 0, 0);
                            if (ts_vendor > eight_hours) table_consultant(rfi, consultant, ts_vendor);                            
                        }
                    }
                    else if (from1 != "Amy Mills")
                    {
                        TimeSpan ts = current - time2;
                        TimeSpan two_weeks = new TimeSpan(14, 0, 0, 0);
                        if (ts > two_weeks) table_old_rfi(rfi, old_rfi, ts);
                    }
                }
            }
            catch (Exception ex)
            {
                string subject = "Error in overdue";
                ErrorHandling.ErrorEmail(subject, ex);
            }
        }

        TimeSpan remove_non_business_hours(DateTime current, DateTime time1)
        {
//            DateTime day = DateTime.Parse(cat_coll[0]["recd"].ToString()).Date;
//            DateTime day2 = DateTime.Parse(cat_coll[cat_coll.Length - 1]["recd"].ToString()).Date;   
            TimeSpan time_of_day_recd = time1.TimeOfDay;
            TimeSpan start_business_day = new TimeSpan(7, 30, 0);
            TimeSpan reduce_hours_to_130 = new TimeSpan(13, 30, 0);
            TimeSpan end_business_day = new TimeSpan(17, 30, 0);
            TimeSpan exclude_hours__after_business_day = new TimeSpan(6, 30, 0);
            TimeSpan reduced_hours = new TimeSpan();
            if (time_of_day_recd < start_business_day) reduced_hours = exclude_hours__after_business_day + time_of_day_recd;
            else if (time_of_day_recd > start_business_day && time_of_day_recd < reduce_hours_to_130) reduced_hours = exclude_hours__after_business_day + time_of_day_recd - (time_of_day_recd - start_business_day);
            else if (time_of_day_recd > end_business_day) reduced_hours = time_of_day_recd - end_business_day;            
            return reduced_hours;
        }

        TimeSpan remove_holidays_weekends(DataRow[] cat_coll, HashSet<DateTime> holidays)
        {
            DateTime day = DateTime.Parse(cat_coll[0]["recd"].ToString()).Date;
            DateTime day2 = DateTime.Parse(cat_coll[cat_coll.Length - 1]["recd"].ToString()).Date;            
            TimeSpan ts = day2 - day;
            int days = ts.Days;
            for (int d = 0; d < days; d++)
            {
                if (day.DayOfWeek == DayOfWeek.Saturday || day.DayOfWeek == DayOfWeek.Sunday || holidays.Contains(Convert.ToDateTime(day)))
                {
                    if (d == 0)
                    {
                        ts.Subtract(day.TimeOfDay);
                    }
                    else ts.Subtract(new TimeSpan(24, 0, 0));
                }
                day = day.AddDays(1);
            }
            return ts;
        }

        TimeSpan number_of_hours(DataRow[] cat_coll)
        {
            DateTime date1 = Convert.ToDateTime(cat_coll[cat_coll.Length - 1]["recd"]);
            DateTime date2 = Convert.ToDateTime(cat_coll[0]["recd"]);
            TimeSpan diff1 = date2.Subtract(date1);
            return diff1;
        }

        private static HashSet<DateTime> GetHolidays(int year)
        {
            HashSet<DateTime> holidays = new HashSet<DateTime>();
            //NEW YEARS 
            DateTime newYearsDate = AdjustForWeekendHoliday(new DateTime(year, 1, 1).Date);
            holidays.Add(newYearsDate);
            //MEMORIAL DAY  -- last monday in May 
            DateTime memorialDay = new DateTime(year, 5, 31);
            DayOfWeek dayOfWeek = memorialDay.DayOfWeek;
            while (dayOfWeek != DayOfWeek.Monday)
            {
                memorialDay = memorialDay.AddDays(-1);
                dayOfWeek = memorialDay.DayOfWeek;
            }
            holidays.Add(memorialDay.Date);

            //INDEPENCENCE DAY 
            DateTime independenceDay = AdjustForWeekendHoliday(new DateTime(year, 7, 4).Date);
            holidays.Add(independenceDay);

            //LABOR DAY -- 1st Monday in September 
            DateTime laborDay = new DateTime(year, 9, 1);
            dayOfWeek = laborDay.DayOfWeek;
            while (dayOfWeek != DayOfWeek.Monday)
            {
                laborDay = laborDay.AddDays(1);
                dayOfWeek = laborDay.DayOfWeek;
            }
            holidays.Add(laborDay.Date);

            //THANKSGIVING DAY - 4th Thursday in November 
            var thanksgiving = (from day in Enumerable.Range(1, 30)
                                where new DateTime(year, 11, day).DayOfWeek == DayOfWeek.Thursday
                                select day).ElementAt(3);
            DateTime thanksgivingDay = new DateTime(year, 11, thanksgiving);
            holidays.Add(thanksgivingDay.Date);

            DateTime christmasDay = AdjustForWeekendHoliday(new DateTime(year, 12, 25).Date);
            holidays.Add(christmasDay);
            return holidays;
        }

        public static DateTime AdjustForWeekendHoliday(DateTime holiday)
        {
            if (holiday.DayOfWeek == DayOfWeek.Saturday)
            {
                return holiday.AddDays(-1);
            }
            else if (holiday.DayOfWeek == DayOfWeek.Sunday)
            {
                return holiday.AddDays(1);
            }
            else
            {
                return holiday;
            }
        }
    }
}
